Oracle常用查询操作 | 您所在的位置:网站首页 › oracle 字段以什么开头 › Oracle常用查询操作 |
--整表查询 例子0:desc 查看表结构; describe s_emp; desc s_dept; 例子1:查询当前Schema中有多少个表? select table_name from tabs; select table_name from user_tables; 例子2:查询s_emp前三个列的内容? select id, first_name, last_name from s_emp; 例子3:显示所有部门的内容? select * from s_dept; 例子4:显示每位员工的全名? select id, first_name||’-’||last_name from s_emp; 例子5:员工全名 is in department 部门编号? select id, first_name||’-’||last_name ||’ is in department ’||dept_id from s_emp; 例子6:列出每个员工的年薪? select id ,first_name, salary*12 from s_emp; 例子7:列出每个员工的一年的总收入? select id, first_name, salary*12*(1+NVL(COMMISSION_PCT,0)/100) "total salary" from s_emp;
例子8:列出所有部门的种类? select distinct name from s_dept; 例子9:列出各部门有什么不同的职位? select distinct dept_id , title from s_emp;
--限定查询 例子10: 列出42部门的员工的id,名字,salary,和部门号? select id, first_name, dept_id from s_emp where dept_id = 42; 例子11: 找出工资高于1200元的员工的年薪? select id, first_name, salary*12 from s_emp where salary > 1200; 例子12: 找出年薪高于12000元的员工? select id, first_name, salary*12 from s_emp where salary*12 > 12000; --- where salary > 1000; 例子13: 找出42部门年薪高于12000元的员工? select id, first_name, salary*12 from s_emp where salary > 1000 and dept_id = 42;
例子14: 找出‘Carmen’每个月的工资? select id, first_name from s_emp where first_name=’Carmen’; 例子15: 把所有职位为‘Stock Clerk’的员工列出? select id, first_name, title from s_emp where title=’Stack Clerk’;
例子16: 找出工资在1500-2000之间的员工? select id,first_name,salary from s_emp where salary >= 1500 and salary = 1000 and dept_id=44or dept_id=42;
select * from s_emp where salary >= 1000 and (dept_id=44or dept_id=42);
select first_name,dept_id from s_emp where dept_id in(42,43)and salary >= 1000;
select first_name,dept_id from s_emp where dept_id = any(42,43)and salary >= 1000;
例子24: 按工资降序显示员工的信息? select id, first_name, salary from s_emp order by salary desc; 例子25: 按提成升序显示员工的信息? select id, first_name, commission_pct from s_emp order by 3 asc;
例子26: 先工资降序,再按提成升序显示员工? select id, first_name, salary, commission_pct from s_emp order by salary desc, commission_pct asc; 例子27: 按年薪降序显示员工? select id, first_name, salary from s_emp order by salary*12 desc;
--Oracle函数 【单值函数】 --字符函数 例子28: 当不知道‘Carmen’在数据库是大小写的时,找出‘Carmen’的工资? select first_name, salary from s_emp where lower(first_name)='carmen'; 例子29:列出每个员工名字(last_name)的最后两个字符? select first_name, length(first_name), substr(first_name, length(first_name)-1,2) from s_emp;
select first_name, substr(first_name, -2,2) from s_emp;
--数值函数 例子30: ROUND (45.923, 2)45.92 ROUND (45.923, 0)46 ROUND (45.923, -1)50 TRUNC (45.923, 2) 45.92 TRUNC (45.923) 45 TRUNC (45.923, -1)40
--日期函数 alter session set nls_date_format='yyyy mm dd hh24:mi:ss'; 例子31: 查出下一天、下一分钟、下一秒的时间 select sysdate as now, sysdate+1/24 as next_hour, sysdate+1/(24*60) as next_minute, sysdate+1/(24*60*60) as next_second from dual; 例子32:求某天是星期几 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 星期一 例子33:找出今年的天数 select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 例子34:今天是一年的第几天 select TO_CHAR(SYSDATE,'DDD'),sysdate from dual 例子35:闰年的处理方法 to_char( last_day( to_date('02' || &year,'mmyyyy') ), 'dd' ) 例子36:按照'yyyy mm dd hh24:mi:ss'的格式,输出42部门员工的入职时间? select id, first_name, to_char(start_date, 'yyyy-mm-dd hh24:mi:ss') from s_emp where dept_id = 42;
例子37:列出5月份入职的员工? select first_name, to_char(start_date,'dd-mon-yyyy hh24:mi:ss') from s_emp where to_char(start_date,'mm') = '05'; --where to_char(start_date,'fmmm') = '5'; --where to_char(start_date,'fmmm') = 5; -- where to_char(start_date,'MONTH') = '5'; -- where to_char(start_date, 'MONTH') ='MAY;
select first_name, to_char(start_date,'dd-mon-yyyy hh24:mi:ss') as startt, to_char(start_date,'MONTH') as datet, length(to_char(start_date,'fmMONTH')) as lengtht from s_emp where rtrim(to_char(start_date,'MONTH')) = 'MAY';
例子38:求出下个月的1号? select last_day(sysdate)+1 from dual; select round(last_day(sysdate),'MONTH') from dual; select add_months(trunc(sysdate,'MONTH'),1) from dual; select trunc(add_months(sysdate,1),'MONTH') from dual; 例子39:找出90年上半年入职的员工信息? select first_name, start_date from s_emp --where round(start_date,’YEAR’) = ‘2010-01-01’; --error --where round(start_date,'year') = '1-1月-90' where to_char(start_date,'mm') 15;
--转换函数 例子41: to_char(); to_number(); to_date();
--组函数 例子42:列出提成的平均值? select avg(commission_pct) from s_emp; 例子43:列出提成的最大值? select max(commission_pct) from s_emp; 例子44:求出有提成的员工个数? select count(commission_pct) from s_emp; count():组函数对非空值进行统计。 select count(*) from s_emp where commission_pct is not null;
例子45:求出42部门的平均工资?(只显示平均工资) select avg(salary) from s_emp where dept_id = 42;
--group by 例子46:求出42部门的平均工资?(显示部门号、平均工资) select max(dept_id), avg(salary) from s_emp where dept_id = 42;
select dept_id, avg(salary) from s_emp where dept_id = 42 group by dept_id; 例子47:求出各部门的平均工资? select dept_id,avg(salary) from s_emp group by dept_id;
select dept_id , sum(salary)||'/'||count(*)||'='||sum(salary)/count(*) as avg from s_emp group by dept_id order by dept_id; 例子48:求出各职位的平均工资?(要求:列出职位名称和平均工资) select title, avg(salary) from s_emp group by title; 例子49:求出各部门不同职位的平均工资? select dept_id,title, avg(salary) from s_emp group by dept_id, title order by dept_id;
--having 例子50:求出平均工资高于2000的员工? select first_name, max(salary) from s_emp --where avg(salary) > 2000; group by first_name having avg(salary) > 2000; 求出部门平均工资高于2000的员工信息? select id, first_name,dept_id from s_emp where dept_id in ( select dept_id from s_emp group by dept_id having avg(salary) >= 2000 ); 测试: select id, first_name,dept_id from s_emp where dept_id in ( select dept_id,avg(salary) from s_emp where salary >= 1000 group by dept_id having avg(salary) >= 1500 order by dept_id asc ); 例子51:求出各部门的平均工资? (要求显示:区域名称、部门编号、部门名称、平均工资)??? select d.id, max(r.name)||'_'||min(d.name) as dept, avg(salary) from s_emp e, s_dept d, s_region r where e.dept_id = d.id and d.region_id = r.id group by d.id order by d.id;
|
CopyRight 2018-2019 实验室设备网 版权所有 |